package com.voja.salonschedule.data;

import java.text.NumberFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;

import com.voja.AndroidUtils.SQLiteHelper;
import com.voja.salonschedule.R;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteStatement;

public class CustomerBalance extends AppModel {
    
    public class BalanceItem {
        public static final int TYPE_IN  = 1;
        public static final int TYPE_OUT = -1;
        
        public Date date;
        public double amount;
        public int type;
    }
    
    public class Balance {
        public double price  = 0;
        public double credit = 0;
        
        public String toString() {
            return value()+"";
        }
        public boolean isPositive() {
            return credit >= price;
        }
        public double value() {
            return credit - price;
        }
        public String formatted() {
            NumberFormat format = NumberFormat.getCurrencyInstance();
            return format.format(value());
        }
        
        public int getColor() {
            int id = isPositive() ? R.color.green : R.color.red;
            return context.getResources().getColor(id);
        }
    }

    public CustomerBalance(Context c) {
        super(c);
    }
    
    public Balance get(String customerName) {
        Balance balance = new Balance(); 
        
        SQLiteHelper db = getConnection();
        db.open();
        Cursor c;
        
        // get price
        c = db.query("SELECT SUM(price) FROM events WHERE customer = ?", new String[]{customerName});
        if (c.moveToFirst()) {
            balance.price = c.getDouble(0);
        }
        c.close();
        
        // get credit
        c = db.query("SELECT SUM(amount) FROM customer_balance WHERE customer = ?", new String[]{customerName});
        if (c.moveToFirst()) {
            balance.credit = c.getDouble(0);
        }
        c.close();
        
        db.close();
        return balance;
    }
    
    public boolean addCredit(String customerName, double amount) {
        
        SQLiteHelper db = getConnection();
        db.open();
        
        String sql = "INSERT INTO `customer_balance`(`customer`, `amount`, `date`) "+
                     "VALUES(?, ?, ?)";
        SQLiteStatement stmt = db.prepareStatement(sql);
        stmt.bindString(1, customerName);
        stmt.bindDouble(2, amount);
        stmt.bindString(3, Event.dateTimeFormat.format(Calendar.getInstance().getTime()));
        boolean saved = stmt.executeInsert() > 0;
        
        db.close();
        
        return saved;
    }
    
    public ArrayList<BalanceItem> details(String customerName) {
        ArrayList<BalanceItem> result = new ArrayList<BalanceItem>();
        
        for (BalanceItem item : prices(customerName)) {
            result.add(item);
        }
        
        for (BalanceItem item : credits(customerName)) {
            result.add(item);
        }
        
        Collections.sort(result, new Comparator<BalanceItem>() {
			@Override
            public int compare(BalanceItem item1, BalanceItem item2) {
	            return item1.date.compareTo(item2.date) * -1;
            }
		});
        
        return result;
    }
    
    public ArrayList<BalanceItem> prices(String customerName) {
        ArrayList<BalanceItem> prices = new ArrayList<BalanceItem>();
        SQLiteHelper db = getConnection();
        db.open();
        
        String sql = "SELECT `date`, price "+
                     "FROM events "+
                     "WHERE customer=" + escapeString(customerName) + " " +
                     "ORDER BY `date` DESC";
        Cursor c = db.query(sql);
        while(c.moveToNext()) {
            BalanceItem item = new BalanceItem();
            try {
                item.date = Event.dateTimeFormat.parse(c.getString(0));
            } catch (ParseException e) {
                item.date = new Date();
            }
            item.type = BalanceItem.TYPE_OUT;
            item.amount = c.getDouble(1);
            
            prices.add(item);
        }
        
        db.close();
        return prices;
    }
    
    public ArrayList<BalanceItem> credits(String customerName) {
        ArrayList<BalanceItem> credits = new ArrayList<BalanceItem>();
        SQLiteHelper db = getConnection();
        db.open();
        
        String sql = "SELECT `date`, amount "+
                     "FROM customer_balance "+
                     "WHERE customer=" + escapeString(customerName) + " "+
                     "ORDER BY `date` DESC";
        Cursor c = db.query(sql);
        while(c.moveToNext()) {
            BalanceItem item = new BalanceItem();
            try {
                item.date = Event.dateTimeFormat.parse(c.getString(0));
            } catch (ParseException e) {
                item.date = new Date();
            }
            item.type = BalanceItem.TYPE_IN;
            item.amount = c.getDouble(1);
            
            credits.add(item);
        }
        
        db.close();
        return credits;
    }
}
